﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Data.SqlClient;
using DataMgr.mode;
using DataMgr.msg;
using DataMgr.File;
using DataMgr.msg.req;

namespace DataMgr
{
    class MyDb
    {
        private MySqlConnection msqlConnection;
        ~MyDb()
        {
            if (null != this.msqlConnection)
                msqlConnection.Close();
        }
        public bool Init()
        {
            //StringBuilder sb = new StringBuilder();
            //string sDbCfgFile = @"..\\Setting\BtsClient.cfg";
            //读取
            //MsgUtil.GetPrivateProfileString("DbCfg", "dbsync.Host", "localhost", sb, 255, sDbCfgFile);
            string sConnectUrl = "server=" + MsgUtil.GetCfgString("DbCfg", "dbsync.Host", "localhost");
            //MsgUtil.GetPrivateProfileString("DbCfg", "dbsync.user", "localhost", sb, 255, sDbCfgFile);
            sConnectUrl += ";user id=" + MsgUtil.GetCfgString("DbCfg", "dbsync.user");
            //MsgUtil.GetPrivateProfileString("DbCfg", "dbsync.pwd", "localhost", sb, 255, sDbCfgFile);
            sConnectUrl += ";password=" + MsgUtil.GetCfgString("DbCfg", "dbsync.pwd");
            //MsgUtil.GetPrivateProfileString("DbCfg", "dbsync.DBname", "localhost", sb, 255, sDbCfgFile);
            sConnectUrl += ";database=" + MsgUtil.GetCfgString("DbCfg", "dbsync.DBname");

            MySql.Data.MySqlClient.MySqlConnection msqlConnection = null;
            //msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=UserName;Password=UserPassword;database=DatabaseName;persist security info=False");
            this.msqlConnection = new MySql.Data.MySqlClient.MySqlConnection(sConnectUrl);
            //define the command reference 
            MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
            //define the connection used by the command object 
            msqlCommand.Connection = this.msqlConnection;
            //define the command text 
            //msqlCommand.CommandText = "SELECT * FROM chl_test_info;";
            try {
                //open the connection  
                this.msqlConnection.Open();
                //MessageBox.Show("数据库已经连接了！");
                //use a DataReader to process each record  
                //MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
                //while (msqlReader.Read())
                //{
                //    //do something with each record  
                //}
            }
            catch (Exception er)
            {
                //do something with the exception 
                //MessageBox.Show("数据库连接失败！");
                Console.Write(er.ToString());
                return false;
            //} finally{
            //    //always close the connection  
            //    this.msqlConnection.Close();
            } 

            return true;
        }

        public List<HisTestInfo> QueryHisTest(Hashtable hashtable)
        {
            if (null == this.msqlConnection)
                Init();

            List<HisTestInfo> list = new List<HisTestInfo>();
            string sSql = "SELECT start_time,unit_no,a.channel_id,testID,bat_code,b.event FROM chl_test_info a INNER  JOIN "+
                "(SELECT (record_seq),event,test_id,channel_id FROM (SELECT * FROM bts_log_chl ORDER BY record_seq DESC) c GROUP BY c.test_id,c.channel_id) AS b "+
                "where a.TestID=b.test_id and a.channel_id=b.channel_id ";
            if (hashtable.ContainsKey("DevId") && hashtable["DevId"].ToString().Length > 0)
            {
                int nDev = Convert.ToInt32(hashtable["DevId"].ToString());
                if (0 != nDev)
                {
                    sSql += " and unit_no=" + hashtable["DevId"].ToString();
                }
            }

            if (hashtable.ContainsKey("ChlId") && hashtable["ChlId"].ToString().Length > 0)
            {
                int nDev = Convert.ToInt32(hashtable["ChlId"].ToString());
                if (0 != nDev)
                {
                    sSql += " and a.channel_id=" + hashtable["ChlId"].ToString();
                }
            }

            if (hashtable.ContainsKey("Barcode"))
            {
                sSql += " and bat_code='" + hashtable["Barcode"].ToString()+"'";
            }

            if (hashtable.ContainsKey("Days"))
            {
                System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1)); // 当地时区
                long lNow = (long)(DateTime.Now - startTime).TotalMilliseconds; // 相差毫秒数
                long lSpan = Convert.ToInt32(hashtable["Days"].ToString());
                lSpan *= 24 * 3600 * 1000;
                sSql += " and start_time<" + (lNow - lSpan);
            }
            else if (hashtable.ContainsKey("FromDate"))
            {
                System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1)); // 当地时区
                DateTime d11t1 = DateTime.FromFileTimeUtc(Convert.ToInt64(hashtable["ToDate"].ToString()));
                DateTime d11t = d11t1.AddDays(1);
                long lEnd = (long)(d11t - startTime).TotalMilliseconds;
                sSql += " and start_time<" + lEnd.ToString();

                DateTime d11t2 = DateTime.FromFileTimeUtc(Convert.ToInt64(hashtable["FromDate"].ToString()));
                long lFrom = (long)(d11t2 - startTime).TotalMilliseconds;
                sSql += " and start_time>" + lFrom.ToString();
            }

            sSql += " order by start_time desc; ";
            MySqlCommand msqlCommand = new MySqlCommand(sSql, this.msqlConnection);

            MySqlDataReader dr;//创建DataReader对象  
            dr = msqlCommand.ExecuteReader(); //msqlCommand.ExecuteReader();//执行查询  
            while (dr.Read())//判断数据表中是否含有数据  
            {
                HisTestInfo test = new HisTestInfo();
                test.DevId = Convert.ToInt32(dr[1].ToString());
                //Console.Write("\n starttime " + dr[0].ToString());
                test.StartTime = Convert.ToDouble(dr[0].ToString());
                test.ChlId = Convert.ToInt32(dr[2].ToString());
                test.TestId = Convert.ToInt32(dr[3].ToString());
                test.Barcode = dr[4].ToString();
                test.LastLog = Convert.ToInt32(dr[5].ToString());
                
                list.Add(test);
            }

            //MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
            ////define the connection used by the command object 
            //msqlCommand.Connection = this.msqlConnection;
            ////define the command text 
            //msqlCommand.CommandText = sSql;
            //MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
            //while (msqlReader.Read())
            //{
            //    //do something with each record  
            //}
            dr.Close();//关闭DataReader对象  

            for (int i = 0; i < list.Count; i++)
            {
                HisTestInfo test = list[i];
                test.RecordCount = GetRecordCount(test.TestId, test.ChlId, test.DevId);
            }

            Console.Write("size = " + list.Count);
            return list;
        }

        ///查询最大记录数
        long GetRecordCount(int testId, int chlId,int devId)
        {
            string sSql = "select MAX(seq_id) FROM data_" + devId.ToString() + "  WHERE chl_id=" + chlId.ToString() + " AND test_id=" + testId.ToString();
            MySqlCommand msqlCommand = new MySqlCommand(sSql, this.msqlConnection);

            long nCount = 0;
            MySqlDataReader dr;//创建DataReader对象  
            dr = msqlCommand.ExecuteReader(); //msqlCommand.ExecuteReader();//执行查询  
            if (dr.HasRows && dr.Read())//判断数据表中是否含有数据  
            {
                string sSeq = dr[0].ToString();
                if (sSeq.Length > 0)
                    nCount = Convert.ToInt64(sSeq);
            }
            dr.Close();//关闭DataReader对象  
            return nCount;
        }

        ///删除测试表数据
        public bool DeleteTestData(HisTestInfo test)
        {
            MySqlCommand msqlCommand = new MySqlCommand();
            //define the connection used by the command object 
            msqlCommand.Connection = this.msqlConnection;

            //删除记录数据
            string sSql = "delete FROM data_" + test.DevId.ToString() + "  WHERE chl_id=" + test.ChlId.ToString() + " AND test_id=" + test.TestId.ToString();
            //MySqlCommand msqlCommand = new MySqlCommand(sSql, this.msqlConnection);
            Console.WriteLine("DeleteTestData: " + sSql);
            msqlCommand.CommandText = sSql;
            msqlCommand.ExecuteNonQuery();

            string sSqlLog = "delete FROM bts_log_chl  WHERE channel_id=" + test.ChlId.ToString() + " AND test_id=" + test.TestId.ToString();
            Console.WriteLine("DeleteTestData: " + sSqlLog);
            msqlCommand.CommandText = sSqlLog;
            msqlCommand.ExecuteNonQuery();

            string sSqlTest = "delete FROM chl_test_info  WHERE channel_id=" + test.ChlId.ToString() + " AND TestID=" + test.TestId.ToString() + " AND unit_no=" + test.DevId.ToString();
            Console.WriteLine("DeleteTestData: " + sSqlTest);
            msqlCommand.CommandText = sSqlTest;
            msqlCommand.ExecuteNonQuery();

            return true;
        }

        //查询基本测试信息
        public bool QueryTestInfo(string guidPath,UInt16 chlNo, ref ChannelTestInfo chlInfo)
        {
            string sSql = @"SELECT ip,bat_code,mid_ver,xwj_ver,unit_no,parallel_count,testID,start_time FROM  
                chl_test_info where guidPath='";
            sSql += guidPath + "' and channel_id=" + chlNo.ToString();
            MySqlCommand msqlCommand = new MySqlCommand(sSql, this.msqlConnection);

            MySqlDataReader dr;//创建DataReader对象  
            dr = msqlCommand.ExecuteReader(); //msqlCommand.ExecuteReader();//执行查询  
            if (dr != null && dr.Read())//判断数据表中是否含有数据  
            {
                chlInfo.devChlInfo.midIp = dr[0].ToString();
                chlInfo.devChlInfo.barcode = dr[1].ToString();
                chlInfo.devChlInfo.midVer = dr[2].ToString();
                chlInfo.devChlInfo.xwjVer = dr[3].ToString();
                string dev = dr[4].ToString();
                string count = dr[5].ToString();
                string testId = dr[6].ToString();
                //chlInfo.devChlInfo.devId = Byte.Parse
                chlInfo.devChlInfo.devId = Byte.Parse(dev);
                chlInfo.devChlInfo.pallelCount = (count.Length > 0) ? Convert.ToByte(count) : (Byte)0;
                chlInfo.devChlInfo.chlId = chlNo;
                chlInfo.devChlInfo.testId = UInt32.Parse(testId);
                chlInfo.StartTime = Convert.ToDouble(dr[7].ToString());
            }
            else
                return false;

            dr.Close();

            return chlInfo.devChlInfo.testId > 0;   
        }
     
        //查询历史记录数据
        public List<SingleHisDfData> QueryTestRecordData(short devId,UInt32 testId, UInt16 chlId, UInt32 begin,
            ref Hashtable bmsTable)
        {
            string sSql2 = @"select seq_id,st_data_count,vol_count,temp_count,self_count,st_data,
                cell_vol_info,cell_temp_info,self_info from bms_data_his WHERE dev_id=";
            sSql2 += devId.ToString() + " and chl_id=" + chlId.ToString() + " and test_id=" + testId.ToString();
            sSql2 += " and seq_id > " + begin.ToString() + " ORDER BY seq_id ASC";
            MySqlCommand msqlCommand2 = new MySqlCommand(sSql2, this.msqlConnection);

            MySqlDataReader dr2;//创建DataReader对象  
            dr2 = msqlCommand2.ExecuteReader(); //msqlCommand.ExecuteReader();//执行查询  
            BmsHisData stdBMS = null;
            while (dr2.Read())//判断数据表中是否含有数据  
            {
                BmsHisData stBms = new BmsHisData();
                stBms.TestSn = UInt32.Parse(dr2["seq_id"].ToString());
                stBms.Flag = 1;
                stBms.StDataCount = UInt16.Parse(dr2["st_data_count"].ToString());
                stBms.VolCount = UInt16.Parse(dr2["vol_count"].ToString());
                stBms.TempCount = UInt16.Parse(dr2["temp_count"].ToString());
                stBms.SelfCount = UInt16.Parse(dr2["self_count"].ToString());
                stBms.StData = new Byte[dr2.GetBytes(5, 0, null, 0, int.MaxValue)];
                stBms.VolData = new Byte[dr2.GetBytes(6, 0, null, 0, int.MaxValue)];
                stBms.TempData = new Byte[dr2.GetBytes(7, 0, null, 0, int.MaxValue)];
                stBms.SelfData = new Byte[dr2.GetBytes(8, 0, null, 0, int.MaxValue)];
                bmsTable[stBms.TestSn] = stBms;
                if (stdBMS == null)
                    stdBMS = stBms;
            }

            dr2.Close();

            List<SingleHisDfData> rList = new List<SingleHisDfData>();

            string sSql = @"select seq_id,stepTime,recordTime,cycle,step_seq,step_id,step_type,test_vol,test_cur,test_tmp,
                test_cap,test_chg_cap,test_dischg_cap,test_eng,test_chg_eng,test_dischg_eng,test_ir,tran_state,
                dtime from data_";
            sSql += devId.ToString() + " where chl_id=" + chlId.ToString() + " and test_id=" + testId.ToString();
            sSql += " and dtime > 0 and seq_id > "+begin.ToString() + " ORDER BY seq_id ASC";
            MySqlCommand msqlCommand = new MySqlCommand(sSql, this.msqlConnection);

            MySqlDataReader dr;//创建DataReader对象  
            dr = msqlCommand.ExecuteReader(); //msqlCommand.ExecuteReader();//执行查询  
            while (dr.Read())//判断数据表中是否含有数据  
            {
                SingleHisDfData stDf = new SingleHisDfData();
                stDf.dwTestSn = UInt32.Parse(dr["seq_id"].ToString());
                stDf.stepTime = UInt64.Parse(dr["stepTime"].ToString());
                stDf.recordTime = UInt64.Parse(dr["recordTime"].ToString());
                stDf.dwCycle = UInt32.Parse(dr["cycle"].ToString());
                stDf.stepSeq = UInt32.Parse(dr["step_seq"].ToString());
                stDf.stepID = UInt32.Parse(dr["step_id"].ToString());
                stDf.btStepType = Byte.Parse(dr["step_type"].ToString());
                stDf.nVolt = Int32.Parse(dr["test_vol"].ToString());
                stDf.nCurr = Int32.Parse(dr["test_cur"].ToString());
                stDf.nTempature = Int16.Parse(dr["test_tmp"].ToString());
                stDf.nCap = Int32.Parse(dr["test_cap"].ToString());
                stDf.dwChargeCap = UInt32.Parse(dr["test_chg_cap"].ToString());
                stDf.dwDisChargeCap = UInt32.Parse(dr["test_dischg_cap"].ToString());
                stDf.nEngSum = Int32.Parse(dr["test_eng"].ToString());
                stDf.dwChargeEng = UInt32.Parse(dr["test_chg_eng"].ToString());
                stDf.dwDisChargeEng = UInt32.Parse(dr["test_dischg_eng"].ToString());
                stDf.nInterRes = Int16.Parse(dr["test_ir"].ToString());
                stDf.nTranState = Int32.Parse(dr["tran_state"].ToString());
                stDf.dTestTime = Double.Parse(dr["dtime"].ToString());
                rList.Add(stDf);

                if (stdBMS != null && !bmsTable.ContainsKey(stDf.dwTestSn)) //有bms且没这个序号的，创建个值填充
                {
                    BmsHisData stBms = new BmsHisData();
                    stBms.TestSn = stDf.dwTestSn;
                    stBms.Flag = 1;
                    stBms.StDataCount = stdBMS.StDataCount;
                    stBms.VolCount = stdBMS.VolCount;
                    stBms.TempCount = stdBMS.TempCount;
                    stBms.SelfCount = stdBMS.SelfCount;
                    if (stdBMS.StData.Length > 0)
                        stBms.StData = new Byte[stdBMS.StData.Length];
                    if (stdBMS.VolData.Length > 0)
                        stBms.VolData = new Byte[stdBMS.VolData.Length];
                    if (stdBMS.TempData.Length > 0)
                        stBms.TempData = new Byte[stdBMS.TempData.Length];
                    if (stdBMS.SelfData.Length > 0)
                        stBms.SelfData = new Byte[stdBMS.SelfData.Length];
                }
            }

            dr.Close();
            return rList;
        }

        //查历史日志
        public List<SingleHisLogData> QueryTestLog(UInt32 testId, UInt16 chlId)
        {
            string sSql = @"select  record_seq,seq_id,test_time,event,event_value from  bts_log_chl where ";
            sSql += " channel_id=" + chlId.ToString() + " and test_id=" + testId.ToString();
            sSql += " ORDER BY test_time ASC,record_seq ASC ";
            MySqlCommand msqlCommand = new MySqlCommand(sSql, this.msqlConnection);

            MySqlDataReader dr;//创建DataReader对象  
            dr = msqlCommand.ExecuteReader(); //msqlCommand.ExecuteReader();//执行查询  
            List<SingleHisLogData> logList = new List<SingleHisLogData>();
            while (dr.Read())//判断数据表中是否含有数据  
            {
                SingleHisLogData log = new SingleHisLogData();
                log.nRecordSeq = Int32.Parse(dr["record_seq"].ToString());
                log.nRefTestSn = UInt32.Parse(dr["seq_id"].ToString());
                log.dTestTime = Double.Parse(dr["test_time"].ToString());
                log.nEvent = Int32.Parse(dr["event"].ToString());
                log.nEventValue = Int64.Parse(dr["event_value"].ToString());
                logList.Add(log);
            }

            dr.Close();
            return logList;
        }

        //查询工步统计数据
        public List<STEP_STATIS> QueryStepStatis(UInt32 testId, UInt16 chlId)
        {
            string sSql = @"select step_id,record_count,step_type,cap,eng,vol_start,vol_end,tmp_start,
                tmp_end,cap_end,first_record_pos,last_record_pos,cycle_id from  step_statis where  ";
            sSql += " channel_id=" + chlId.ToString() + " and test_id=" + testId.ToString();
            sSql += " ORDER BY step_id ASC ";
            MySqlCommand msqlCommand = new MySqlCommand(sSql, this.msqlConnection);

            MySqlDataReader dr;//创建DataReader对象  
            dr = msqlCommand.ExecuteReader(); //msqlCommand.ExecuteReader();//执行查询  
            List<STEP_STATIS> stepList = new List<STEP_STATIS>();
            while (dr.Read())//判断数据表中是否含有数据  
            {
                STEP_STATIS step = new STEP_STATIS();
                step.dwStepID = UInt32.Parse(dr["step_id"].ToString());
                step.nRecordsCount = UInt32.Parse(dr["record_count"].ToString());
                step.btStepType = Byte.Parse(dr["step_type"].ToString());
                step.dCap = Double.Parse(dr["cap"].ToString());
                step.dEng = Double.Parse(dr["eng"].ToString());
                step.dVolStart = Double.Parse(dr["vol_start"].ToString());
                step.dVolEnd = Double.Parse(dr["vol_end"].ToString());
                step.dTmpStart = Double.Parse(dr["tmp_start"].ToString());
                step.dTmpEnd = Double.Parse(dr["tmp_end"].ToString());
                step.dCapEnd = Double.Parse(dr["cap_end"].ToString());

                step.dwFirstRecordIndex = UInt32.Parse(dr["first_record_pos"].ToString());
                step.dwLastRecordIndex = UInt32.Parse(dr["last_record_pos"].ToString());
                step.dwCycleID = UInt32.Parse(dr["cycle_id"].ToString());
                stepList.Add(step);
            }

            dr.Close();
            return stepList;
        }


        //容量、时间、电量、终压、终流,     开压、均压、
        public List<string> QueryStepEndValue(int devNo, UInt32 testId,UInt32 chlNo, int stepNo, int capType)
        {
            string sSql = @"select test_cap,stepTime,test_eng,test_vol,test_cur from data_";
            sSql += devNo.ToString() + " where chl_id=" + chlNo.ToString() + " and test_id=" + testId.ToString();
            sSql += " and step_id=" + stepNo.ToString() + " ORDER BY seq_id DESC limit 1 ";
            MySqlCommand msqlCommand = new MySqlCommand(sSql, this.msqlConnection);

            MySqlDataReader dr;//创建DataReader对象  
            List<string> listResult = new List<string>();
            dr = msqlCommand.ExecuteReader(); //msqlCommand.ExecuteReader();//执行查询  
            if (dr == null)
                return listResult;

            if (dr.Read())//判断数据表中是否含有数据  
            {
                if ((capType & (int)CapSelType.CapSel_Capacity) > 0)
                    listResult.Add(dr["test_cap"].ToString());
                if ((capType & (int)CapSelType.CapSel_Time) > 0)
                    listResult.Add(dr["stepTime"].ToString());
                if ((capType & (int)CapSelType.CapSel_Energy) > 0)
                    listResult.Add(dr["test_eng"].ToString());
                if ((capType & (int)CapSelType.CapSel_EndVol) > 0)
                    listResult.Add(dr["test_vol"].ToString());
                if ((capType & (int)CapSelType.CapSel_EndCur) > 0)
                    listResult.Add(dr["test_cur"].ToString());
            }

            dr.Close();
            return listResult;
        }

        //开压、
        public List<string> QueryStepBeginValue(int devNo, UInt32 testId, UInt32 chlNo, int stepNo,int capType )
        {
            string sSql = @"select test_vol from data_";
            sSql += devNo.ToString() + " where chl_id=" + chlNo.ToString() + " and test_id=" + testId.ToString();
            sSql += " and step_id=" + stepNo.ToString() + " ORDER BY seq_id ASC limit 1 ";
            MySqlCommand msqlCommand = new MySqlCommand(sSql, this.msqlConnection);

            MySqlDataReader dr;//创建DataReader对象  
            List<string> listResult = new List<string>();
            dr = msqlCommand.ExecuteReader(); //msqlCommand.ExecuteReader();//执行查询  
            if (dr.Read())//判断数据表中是否含有数据  
            {
                if ((capType & (int)CapSelType.CapSel_OpenVol) > 0)
                    listResult.Add(dr["test_vol"].ToString());
            }

            dr.Close();
            return listResult;
        }

        public string GetPassword(string user)
        {
            string sSql = @"select password from user_info where user_id='"+ user + "';"; 
            MySqlCommand msqlCommand = new MySqlCommand(sSql, this.msqlConnection);

            MySqlDataReader dr;//创建DataReader对象  
            string pwd = "";
            dr = msqlCommand.ExecuteReader(); //msqlCommand.ExecuteReader();//执行查询  
            if (dr.Read())//判断数据表中是否含有数据  
            {
                pwd = dr["password"].ToString();
            }

            dr.Close();
            return pwd;
        }
    }



}
